package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.ApparenceData;
import agenceVoyageVirtuel.client.beans.AvatarData;
import agenceVoyageVirtuel.client.beans.CarteData;
import agenceVoyageVirtuel.client.beans.ClientData;
import agenceVoyageVirtuel.client.beans.CommandeData;
import agenceVoyageVirtuel.client.beans.MetierData;
import agenceVoyageVirtuel.client.beans.ReputationData;
import agenceVoyageVirtuel.client.beans.SexeData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Avatar extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Avatar() {
		// nothing to do
	}
	
	public List<AvatarData> getAvatars(int idCommande) {
		String query = "SELECT t_avatar.idAvatar, age, mensurations, taille, poids, t_apparence.idApparence, libApparence, t_Metier.idMetier, libMetier, t_reputation.idReputation, libReputation, t_sexe.idSexe, libSexe"
			+ " FROM t_avatars JOIN t_avatar, t_apparence, t_metier, t_reputation, t_sexe"
			+ " WHERE idCommande=" + idCommande
			+ " AND t_avatars.idAvatar=t_avatar.idAvatar"
			+ " AND t_avatar.idApparence=t_apparence.idApparence"
			+ " AND t_avatar.idMetier=t_metier.idMetier"
			+ " AND t_avatar.idReputation=t_reputation.idReputation"
			+ " AND t_avatar.idSexe=t_sexe.idSexe;";

		// prepare for rpc transport
		List<AvatarData> avatarDatas = new ArrayList<AvatarData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				avatarDatas.add(new AvatarData());

				avatarDatas.get(i).setIdAvatar(result.getInt(1));
				avatarDatas.get(i).setAge(result.getInt(2));
				avatarDatas.get(i).setMensurations(result.getString(3));
				avatarDatas.get(i).setTaille(result.getInt(4));
				avatarDatas.get(i).setPoids(result.getInt(5));
				//Apparence
				avatarDatas.get(i).setApparence(new ApparenceData());
				avatarDatas.get(i).getApparence().setIdApparence(result.getInt(6));
				avatarDatas.get(i).getApparence().setLibApparence(result.getString(7));
				//Metier
				avatarDatas.get(i).setMetier(new MetierData());
				avatarDatas.get(i).getMetier().setIdMetier(result.getInt(8));
				avatarDatas.get(i).getMetier().setLibMetier(result.getString(9));
				//Reputation
				avatarDatas.get(i).setReputation(new ReputationData());
				avatarDatas.get(i).getReputation().setIdReputation(result.getInt(10));
				avatarDatas.get(i).getReputation().setLibReputation(result.getString(11));
				//Sexe
				avatarDatas.get(i).setSexe(new SexeData());
				avatarDatas.get(i).getSexe().setIdSexe(result.getInt(12));
				avatarDatas.get(i).getSexe().setLibSexe(result.getString(13));
				
				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return avatarDatas;		
	}	
	
	public List<AvatarData> getAvatarsUtilisees(ClientData clientCourant) {
		String query = "SELECT t_avatar.idAvatar, age, mensurations, taille, poids, t_apparence.idApparence, libApparence, t_Metier.idMetier, libMetier, t_reputation.idReputation, libReputation, t_sexe.idSexe, libSexe"
			+ " FROM t_commande JOIN t_avatars, t_avatar, t_apparence, t_metier, t_reputation, t_sexe"
			+ " WHERE idClient=" + clientCourant.getIdClient()
			+ " AND t_commande.idCommande=t_avatars.idCommande"
			+ " AND t_avatars.idAvatar=t_avatar.idAvatar"
			+ " AND t_avatar.idApparence=t_apparence.idApparence"
			+ " AND t_avatar.idMetier=t_metier.idMetier"
			+ " AND t_avatar.idReputation=t_reputation.idReputation"
			+ " AND t_avatar.idSexe=t_sexe.idSexe;";

		// prepare for rpc transport
		List<AvatarData> avatarDatas = new ArrayList<AvatarData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				avatarDatas.add(new AvatarData());

				avatarDatas.get(i).setIdAvatar(result.getInt(1));
				avatarDatas.get(i).setAge(result.getInt(2));
				avatarDatas.get(i).setMensurations(result.getString(3));
				avatarDatas.get(i).setTaille(result.getInt(4));
				avatarDatas.get(i).setPoids(result.getInt(5));
				//Apparence
				avatarDatas.get(i).setApparence(new ApparenceData());
				avatarDatas.get(i).getApparence().setIdApparence(result.getInt(6));
				avatarDatas.get(i).getApparence().setLibApparence(result.getString(7));
				//Metier
				avatarDatas.get(i).setMetier(new MetierData());
				avatarDatas.get(i).getMetier().setIdMetier(result.getInt(8));
				avatarDatas.get(i).getMetier().setLibMetier(result.getString(9));
				//Reputation
				avatarDatas.get(i).setReputation(new ReputationData());
				avatarDatas.get(i).getReputation().setIdReputation(result.getInt(10));
				avatarDatas.get(i).getReputation().setLibReputation(result.getString(11));
				//Sexe
				avatarDatas.get(i).setSexe(new SexeData());
				avatarDatas.get(i).getSexe().setIdSexe(result.getInt(12));
				avatarDatas.get(i).getSexe().setLibSexe(result.getString(13));
				
				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return avatarDatas;
	}

	public void commanderAvatars(CommandeData commandeEnCours) {
		for(AvatarData avatar : commandeEnCours.getAvatars()) {
			String query = "INSERT INTO t_avatars (idCommande, idAvatar)"
					+ " VALUES (" + commandeEnCours.getIdCommande()
					+ ", " + avatar.getIdAvatar()
					+ ");";
		
			try {
				Connection connection = getConn();
				Statement insert = connection.createStatement();
				
	            insert.executeUpdate(query);
	            
	            insert.close();
				connection.close();
			} catch(Exception e) {
				System.err.println("Mysql Statement Error: " + query);
				e.printStackTrace();
			}				
		}
	}
	
	private int chercherAvatar(AvatarData avatar) {
		String query = "SELECT idAvatar"
				+ " FROM t_avatar"
				+ " WHERE idApparence=" + avatar.getIdAvatar()
				+ " AND idMetier=" + avatar.getMetier().getIdMetier()
				+ " AND idReputation=" + avatar.getReputation().getIdReputation()
				+ " AND idSexe=" + avatar.getSexe().getIdSexe()
				+ " AND age=" + avatar.getAge()
				+ " AND mensurations=" + avatar.getMensurations()
				+ " AND taille=" + avatar.getTaille()
				+ " AND poids=" + avatar.getPoids()
				+ ";";
	
		// prepare for rpc transport
		int id = -1;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			
			id = result.getInt(1);
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return id;		
	}
	
	public void creerAvatar(AvatarData avatar) {
		avatar.setIdAvatar(chercherAvatar(avatar));
		
		if(avatar.getIdAvatar() != -1)
			return;
			
		String query = "INSERT INTO t_avatar(idApparence, idMetier, idReputation, idSexe, age, mensurations, taille, poids)"
				+ " VALUES (" + avatar.getApparence().getIdApparence()
				+ ", " + avatar.getMetier().getIdMetier()
				+ ", " + avatar.getReputation().getIdReputation()
				+ ", " + avatar.getSexe().getIdSexe()
				+ ", " + avatar.getAge()
				+ ", " + avatar.getMensurations()
				+ ", " + avatar.getTaille()
				+ ", " + avatar.getPoids()
				+ ");";
		
		try {
			Connection connection = getConn();
			PreparedStatement  prepareStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			prepareStatement.executeUpdate();
	        ResultSet keys = prepareStatement.getGeneratedKeys();
	        
	        while(keys.next()) { 
	        	avatar.setIdAvatar(keys.getInt(1)); //idAvatar
	        	break;
	        }
			
	        keys.close();
	        prepareStatement.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}
		
		return;		
	}
}
